package vip.xiaonuo.smzq.modular.hisi.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.hisi.entity.KsZyclhz;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.CxtjgjzdVo;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.KsQueryCriteria;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.KsZyclVo;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.ZyclCalculate;

import java.util.List;

/**
 * 矿山资源储量汇总 Mapper
 *
 * @author sqq
 * @since 2024-07-04
 */
@Mapper
public interface KsZyclhzMapper extends BaseMapper<KsZyclhz> {
      @Select("select z.kcdm,z.tjdx,z.kqbh,z.djflbh,z.nd \n" +
              "FROM kcl_ks s   left join kcl_zycl z on z.kqbh=s.kqbh  and z.nd=s.nd \n" +
              "where s.nd=#{nd} and s.xzqdm::VARCHAR like CONCAT('%'|| #{xzqbh} || '%') and z.kqbh!='' \n" +
              "GROUP BY z.kcdm,z.tjdx,z.kqbh,z.djflbh,z.nd \n" +
              "ORDER BY z.kqbh")
      List<KsQueryCriteria> synchronizingKsData(@Param("nd") Integer nd, @Param("xzqbh") String xzqbh);

    @Select("WITH kcl_zycl_filtered AS (\n" +
            "  SELECT z.kcdm, z.tjdx, SUM( CASE  WHEN c.jsldw IS NOT NULL AND c.jsldw <> '' THEN z.jsl ELSE z.ksl END) AS total_sum\n" +
            "  FROM kcl_zycl z left join kcl_cb06_kc c on (z.kcdm=c.kcdm and c.tjdx=z.tjdx)\n" +
            "  WHERE z.zycllb = 19002 AND  z.nd=#{nd} and z.kqbh=#{kqbh} and z.kcdm=#{kcdm} and z.tjdx=#{tjdx} and z.zycllx in ('TM','KZ','TD')\n" +
            "  GROUP BY z.kcdm, z.tjdx\n" +
            ")\n" +
            "SELECT \n" +
            "  CASE \n" +
            "    WHEN (kcl_zycl_filtered.total_sum / kcl_gmdz.dwhs ) BETWEEN 0 AND zxxx THEN '小型'\n" +
            "    WHEN (kcl_zycl_filtered.total_sum / kcl_gmdz.dwhs ) BETWEEN zxxx AND dxxx THEN '中型'\n" +
            "    ELSE '大型'\n" +
            "  END AS size_category\n" +
            "FROM \n" +
            "  kcl_zycl_filtered\n" +
            "JOIN \n" +
            "  kcl_cb15_kczyclgmdz AS kcl_gmdz\n" +
            "ON \n" +
            "  kcl_zycl_filtered.kcdm = kcl_gmdz.kcdm\n" +
            "  AND kcl_zycl_filtered.tjdx = kcl_gmdz.tjdx;")
    String getGm(@Param("nd") Integer nd, @Param("kqbh")String kqbh, @Param("kcdm")Integer kcdm, @Param("tjdx")Integer tjdx, @Param("djflbh")Integer djflbh);

    /**
     * 计算是否开采，是否利用资源储量总量的sql
     * @param nd
     * @param kqbh
     * @param kcdm
     * @param tjdx
     * @return
     */
    @Select("with \n" +
            "    kcljsl AS (SELECT COALESCE(SUM(jsl), 0) AS kcljsl from kcl_zycl where zycllx in ('TM','KZ','TD') and zycllb=19004   and nd=#{nd} and kqbh=#{kqbh} and kcdm=#{kcdm} and tjdx=#{tjdx}),\n" +
            "\t\tkclksl AS (SELECT COALESCE(SUM(ksl), 0) AS kclksl from kcl_zycl where zycllx in ('TM','KZ','TD') and zycllb=19004   and nd=#{nd} and kqbh=#{kqbh} and kcdm=#{kcdm} and tjdx=#{tjdx}),\n" +
            "\t\tbyljsl AS (SELECT COALESCE(SUM(jsl), 0) AS byljsl from kcl_zycl where zycllx in ('TM','KZ','TD') and zycllb=19001   and nd=#{nd} and kqbh=#{kqbh} and kcdm=#{kcdm} and tjdx=#{tjdx} ),\n" +
            "\t\tbulksl AS (SELECT COALESCE(SUM(ksl), 0) AS bulksl from kcl_zycl where zycllx in ('TM','KZ','TD') and zycllb=19001   and nd=#{nd} and kqbh=#{kqbh} and kcdm=#{kcdm} and tjdx=#{tjdx}),\n" +
            "\t\tljjsl AS (SELECT COALESCE(SUM(jsl), 0) AS ljjsl from kcl_zycl where zycllx in ('TM','KZ','TD')   and zycllb=19002   and nd=#{nd} and kqbh=#{kqbh} and kcdm=#{kcdm} and tjdx=#{tjdx}),\n" +
            "\t\tliksl AS (SELECT COALESCE(SUM(ksl), 0) AS liksl from kcl_zycl where zycllx in ('TM','KZ','TD')   and zycllb=19002   and nd=#{nd} and kqbh=#{kqbh} and kcdm=#{kcdm} and tjdx=#{tjdx})\n" +
            "select * from \t\tkcljsl,kclksl,byljsl,bulksl,ljjsl,liksl")
    ZyclCalculate getZyclCalculate(@Param("nd") Integer nd,@Param("kqbh") String kqbh,@Param("kcdm") Integer kcdm,@Param("tjdx") Integer tjdx);





    @Select("-- 上表数据\n" +
            "with sbsj as \n" +
            "(-- 基础数据\n" +
            "with base as (\n" +
            "SELECT ks.kqbh, ks.djflbh, zy.kcdm, zy.tjdx, \n" +
            "  sum (cast (case when zy.zycllb = 19001 and zy.zycllx in ('TM', 'KZ', 'TD')  then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as nmby,\n" +
            "  sum (cast (case when zy.zycllb = 19002 and zy.zycllx in ('TM', 'KZ', 'TD')  then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as ljcm,\n" +
            "  sum (cast (case when zy.zycllb = 19002 and zy.zycllx in ('TM', 'KZ', 'TD')  then (case when kc.gmbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as gml,\n" +
            "  sum (cast (case when zy.zycllb = 19004 and zy.zycllx in ('TM', 'KZ', 'TD')  then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as ccl\n" +
            "FROM kcl_ks ks \n" +
            "JOIN kcl_zycl zy ON zy.nd = ks.nd AND zy.kqbh = ks.kqbh AND zy.djflbh = ks.djflbh\n" +
            "left join kcl_cb06_kc kc on zy.kcdm = kc.kcdm and zy.tjdx = kc.tjdx\n" +
            "WHERE ks.nd = #{nd} and ks.sbbs<>3\n" +
            "GROUP BY ks.kqbh, ks.djflbh,  zy.kcdm, zy.tjdx\n" +
            "),\n" +
            "\n" +
            "-- 矿山信息\n" +
            "ksbase as (\n" +
            "select ks.nd, ks.kqbh, ks.djflbh, ks.ksbh, jj.ksgm, jj.ksgmm, jj.sczt, jj.scztm, ks.sbbs\n" +
            "from kcl_ks ks\n" +
            "left join kcl_ksjj jj on ks.kqbh=jj.kqbh and ks.djflbh = jj.djflbh and ks.nd = jj.nd \n" +
            "and (ks.ksbh = jj.ksbh or (ks.ksbh is null and jj.ksbh is null) or (ks.ksbh='' and jj.ksbh=''))\n" +
            "where ks.nd = #{nd} and ks.sbbs<>3\n" +
            "),\n" +
            "\n" +
            "\n" +
            "-- 报告字段\n" +
            "bg as\n" +
            "(\n" +
            "with T as (\n" +
            "  SELECT\n" +
            "    ks.kqbh, bg.bgsxh, bg.kcjdm, bg.klyqkm, bg.bgmc\n" +
            "  FROM\n" +
            "    kcl_ks ks\n" +
            "    LEFT JOIN kcl_bg bg ON ks.kqbh = bg.kqbh AND ks.djflbh = bg.djflbh \n" +
            "  WHERE\n" +
            "    ks.nd = #{nd} AND ks.djflbh = 1000 and bg.nd =#{nd} \n" +
            "),\n" +
            "\n" +
            "tmp as (\n" +
            "select kqbh, max(bgsxh) last_bg, max(case when klyqkm in (1,2,3,4,5,12) THEN 1 ELSE 0 END) yly from T\n" +
            "group by kqbh\n" +
            ")\n" +
            "\n" +
            "select T.kqbh, CASE\n" +
            "    WHEN kcjdm IN ( 4, 103 ) THEN 2  \n" +
            "    WHEN kcjdm IN ( 3, 102 ) THEN 1" +
            "    when kcjdm = 105 and bgmc like '%利用现状%' then 0\n" +
            "    when kcjdm = 105 and bgmc like '%详查%' then 1\n" +
            "    when kcjdm = 105 and bgmc not like '%勘探区%' and bgmc like '%勘探%' then 2\n" +
            "    else 0\n" +
            "  END kcjd, yly from \n" +
            "T \n" +
            "join tmp on T.kqbh = tmp.kqbh and T.bgsxh = tmp.last_bg\n" +
            "),\n" +
            "\n" +
            "\n" +
            "-- 规模\n" +
            "gm as (\n" +
            "SELECT gmt.kqbh, gmt.kcdm, gmt.tjdx, \n" +
            "       CASE\n" +
            "           WHEN (gmt.gml / BZ.dwhs) < BZ.zxxx THEN 0\n" +
            "           WHEN (gmt.gml / BZ.dwhs) >= BZ.zxxx AND (gmt.gml / BZ.dwhs) < BZ.dxxx THEN 1\n" +
            "           ELSE 2\n" +
            "       END gm\n" +
            "from\n" +
            "(\n" +
            "select kqbh, kcdm,tjdx, sum(gml) gml from base \n" +
            "group by kqbh, kcdm,tjdx \n" +
            ") gmt\n" +
            "left join kcl_cb15_kczyclgmdz BZ ON gmt.kcdm = BZ.kcdm AND gmt.tjdx = BZ.tjdx\n" +
            "),\n" +
            "\n" +
            "\n" +
            "-- 利用情况\n" +
            "sfly as(\n" +
            "with T1 AS (    \n" +
            "    select kqbh, kcdm,tjdx, \n" +
            "        sum(nmby) nmby, sum(ljcm) ljcm, \n" +
            "        sum(case when base.djflbh=1000 then nmby else 0 end) nmby_1, sum(case when base.djflbh=1000 then ljcm else 0 end) ljcm_1\n" +
            "        from base\n" +
            "        group by kqbh, kcdm,tjdx\n" +
            "    \n" +
            "),\n" +
            "\n" +
            "-- 利用条件2：矿山（1001到3999之间）的数量；\n" +
            "T2 AS (\n" +
            "    SELECT kqbh, SUM(CASE WHEN djflbh BETWEEN 2000 AND 3999 THEN 1 ELSE 0 END) AS kss\n" +
            "    FROM ksbase WHERE sbbs <> 3\n" +
            "    GROUP BY kqbh\n" +
            ")\n" +
            "\n" +
            "-- -- 利用条件3：最新报告的写明；在bg表中\n" +
            "\n" +
            "SELECT T1.kqbh ,T1.kcdm, T1.tjdx, \n" +
            "       CASE WHEN ((T1.nmby<>T1.ljcm and T1.nmby_1<>T1.ljcm_1) OR T2.kss>0 OR bg.yly>0) THEN 1 ELSE 0 END AS sfly\n" +
            "FROM T1\n" +
            "LEFT JOIN T2 ON T1.kqbh = T2.kqbh\n" +
            "LEFT JOIN bg ON T1.kqbh = bg.kqbh\n" +
            "),\n" +
            "\n" +
            "-- 矿产组合\n" +
            "kczh as (\n" +
            "-- 按照矿产地+矿山规模码统计资源量；\n" +
            "with \n" +
            "tmp as (\n" +
            "SELECT ks.kqbh, ks.djflbh, zy.kcdm, zy.tjdx, zy.kczhm,\n" +
            "  sum (cast (case when zy.zycllb = 19001 and zy.zycllx in ('TM', 'KZ', 'TD') then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as zyl\n" +
            "FROM kcl_ks ks \n" +
            "LEFT JOIN kcl_zycl zy ON zy.nd = ks.nd AND zy.kqbh = ks.kqbh AND zy.djflbh = ks.djflbh\n" +
            "left join kcl_cb06_kc kc on zy.kcdm = kc.kcdm and zy.tjdx = kc.tjdx\n" +
            "WHERE ks.nd = #{nd}  and ks.kqbh is not null and ks.kqbh <> ''\n" +
            "GROUP BY ks.kqbh, ks.djflbh, zy.kcdm, zy.tjdx,  zy.kczhm\n" +
            "),\n" +
            "\n" +
            "\n" +
            " \n" +
            "zy_rank as (\n" +
            "select *,\n" +
            "    case when djflbh = 1000 then 1 else 2 end as yxj,\n" +
            "    rank() over (PARTITION by kqbh, kcdm, tjdx order by zyl desc, djflbh = 1000 desc) as lyrank\n" +
            "    from tmp\n" +
            ")\n" +
            "\n" +
            " \n" +
            "select \n" +
            "tmp.kqbh,tmp.kcdm, tmp.tjdx, max(zy_rank.kczhm) kczhm\n" +
            "from \n" +
            "(\n" +
            "select t1.kqbh,t1.kcdm, t1.tjdx,\n" +
            "    case \n" +
            "      when exists (select 1 from zy_rank t2 where t2.kqbh = t1.kqbh and t2.kcdm = t1.kcdm and t2.tjdx = t1.tjdx  and t2.djflbh =1000) then \n" +
            "      (select min(lyrank) from zy_rank t2 where t2.kqbh = t1.kqbh and t2.kcdm = t1.kcdm and t2.tjdx = t1.tjdx and t2.djflbh =1000) else min(lyrank) end min_r\n" +
            "from zy_rank t1 group by t1.kqbh, t1.kcdm, t1.tjdx\n" +
            ") tmp \n" +
            "join zy_rank\n" +
            "on zy_rank.kqbh = tmp.kqbh and zy_rank.kcdm = tmp.kcdm and zy_rank.tjdx = tmp.tjdx and zy_rank.lyrank = tmp.min_r\n" +
            "group by tmp.kqbh,tmp.kcdm, tmp.tjdx\n" +
            ")\n" +
            "\n" +
            "\n" +
            "select ksbase.nd, ksbase.kqbh, ksbase.djflbh, ksbase.ksbh, base.kcdm, base.tjdx, \n" +
            "    case when bg.kcjd is null then 0 else bg.kcjd end kcjd, \n" +
            "    case when base.ccl = 0 or base.ccl is null then 0 else 1 end kcqk, \n" +
            "    gm.gm,\n" +
            "    sfly.sfly,\n" +
            "    kczh.kczhm,\n" +
            "    t16.mc kczh,\n" +
            "    ksbase.ksgm,\n" +
            "    ksbase.ksgmm,\n" +
            "    ksbase.sczt,\n" +
            "    ksbase.scztm,\n" +
            "    t71.mc sbbs,\n" +
            "    ksbase.sbbs sbbsm\n" +
            "    \n" +
            "  from base\n" +
            "  left join ksbase on ksbase.kqbh = base.kqbh and ksbase.djflbh = base.djflbh \n" +
            "  left join bg on ksbase.kqbh = bg.kqbh\n" +
            "  left join gm on ksbase.kqbh = gm.kqbh and base.kcdm = gm.kcdm and base.tjdx = gm.tjdx\n" +
            "  left join sfly on ksbase.kqbh = sfly.kqbh and base.kcdm = sfly.kcdm and base.tjdx = sfly.tjdx\n" +
            "  left join kczh on ksbase.kqbh = kczh.kqbh and base.kcdm = kczh.kcdm and base.tjdx = kczh.tjdx\n" +
            "  left join kcl_cb01_zh t71 on ksbase.sbbs = t71.bnm and t71.bh=71\n" +
            "  left join kcl_cb01_zh t16 on kczh.kczhm = t16.bnm and t16.bh=16\n" +
            "  where base.kqbh is not null and base.kqbh <> ''\n" +
            "),\n" +
            "\n" +
            "-- 未上表数据\n" +
            "wsbsj as (\n" +
            "-- 矿山信息\n" +
            "with ksbase as (\n" +
            "select ks.nd, ks.kqbh, ks.djflbh, ks.ksbh, max(jj.ksgm) ksgm, max(jj.ksgmm) ksgmm, max(jj.sczt) sczt, max(jj.scztm) scztm, max(ks.sbbs) sbbs\n" +
            "from kcl_ks ks \n" +
            "left join kcl_ksjj jj on ks.ksbh = jj.ksbh and ks.nd = jj.nd \n" +
            "and (ks.kqbh = jj.kqbh or (ks.ksbh is null and  jj.ksbh is null))\n" +
            "where ks.nd = #{nd} and ks.sbbs=3 \n" +
            "group by ks.nd, ks.kqbh, ks.djflbh, ks.ksbh\n" +
            "),\n" +
            "\n" +
            "base as (\n" +
            "SELECT ks.ksbh, zy.kqbh, zy.kcdm, zy.tjdx,\n" +
            "  sum (cast (case when zy.zycllb = 19001 and zy.zycllx in ('TM', 'KZ', 'TD') then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as nmby,\n" +
            "  sum (cast (case when zy.zycllb = 19002 and zy.zycllx in ('TM', 'KZ', 'TD')  then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as ljcm,\n" +
            "  sum (cast (case when zy.zycllb = 19002 and zy.zycllx in ('TM', 'KZ', 'TD')  then (case when kc.gmbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as gml,\n" +
            "  sum (cast (case when zy.zycllb = 19004 and zy.zycllx in ('TM', 'KZ', 'TD') then (case when kc.xsbs =1 then zy.ksl else zy.jsl end) else 0 end as DECIMAL(16,2))) as ccl\n" +
            "FROM kcl_ks ks \n" +
            "LEFT JOIN kcl_zycl zy ON zy.nd = ks.nd AND zy.ksbh = ks.ksbh \n" +
            "and (ks.kqbh = zy.kqbh or (ks.ksbh is null and  zy.ksbh is null))\n" +
            "left join kcl_cb06_kc kc on zy.kcdm = kc.kcdm and zy.tjdx = kc.tjdx\n" +
            "WHERE ks.nd = #{nd} and ks.sbbs=3\n" +
            "group by ks.ksbh, zy.kqbh, zy.kcdm, zy.tjdx\n" +
            "),\n" +
            "\n" +
            "\n" +
            "-- 报告字段\n" +
            "bg as\n" +
            "(\n" +
            "with T as (\n" +
            "  SELECT\n" +
            "    ks.ksbh, ks.kqbh, bg.bgsxh, bg.kcjdm, bg.klyqkm, bg.bgmc\n" +
            "  FROM\n" +
            "    kcl_ks ks\n" +
            "    LEFT JOIN kcl_bg bg ON ks.ksbh = bg.ksbh\n" +
            "    AND  (ks.kqbh = bg.kqbh or (ks.kqbh is null and  bg.kqbh is null))\n" +
            "  WHERE\n" +
            "     ks.nd = #{nd} AND bg.nd =#{nd} and ks.sbbs=3\n" +
            "),\n" +
            "\n" +
            "tmp as (\n" +
            "select ksbh, kqbh, max(bgsxh) last_bg, max(case when klyqkm in (1,2,3,4,5,12) THEN 1 ELSE 0 END) yly from T\n" +
            "group by ksbh, kqbh\n" +
            ")\n" +
            "\n" +
            "select T.ksbh, T.kqbh, CASE\n" +
            "    WHEN kcjdm IN ( 4, 103 ) THEN 2  \n" +
            "    WHEN kcjdm IN ( 3, 102 ) THEN 1\n" +
            "    when kcjdm = 105 and bgmc like '%详查%' then 1\n" +
            "    when kcjdm = 105 and bgmc like '%勘探%' then 2\n" +
            "    else 0\n" +
            "  END kcjd, yly from \n" +
            "T \n" +
            "join tmp on T.ksbh = tmp.ksbh  AND  (T.kqbh = tmp.kqbh or (T.kqbh is null and tmp.kqbh is null)) and  T.bgsxh = tmp.last_bg\n" +
            ")\n" +
            "\n" +
            "\n" +
            "select distinct ksbase.nd, ksbase.kqbh, ksbase.djflbh, ksbase.ksbh, base.kcdm, base.tjdx,\n" +
            "  case when bg.kcjd is null then 0 else bg.kcjd end kcjd,\n" +
            "  case when base.ccl = 0 or base.ccl is null then 0 else 1 end kcqk,\n" +
            "  -1 gm,\n" +
            "  -1 sfly,\n" +
            "  -1 kczhm,\n" +
            "  null kczh,\n" +
            "  ksbase.ksgm,\n" +
            "  ksbase.ksgmm,\n" +
            "  ksbase.sczt,\n" +
            "  ksbase.scztm,\n" +
            "  t71.mc sbbs,\n" +
            "  ksbase.sbbs sbbsm\n" +
            "  from ksbase \n" +
            "  left join base on ksbase.ksbh = base.ksbh and ksbase.kqbh = base.kqbh or (ksbase.kqbh is null and base.kqbh is null)\n" +
            "  left join bg on bg.ksbh = base.ksbh and bg.kqbh = base.kqbh or (bg.kqbh is null and base.kqbh is null)\n" +
            "  left join kcl_cb01_zh t71 on ksbase.sbbs = t71.bnm and t71.bh=71\n" +
            ")\n" +
            "\n" +
            "\n" +
            "select * from sbsj\n" +
            "union\n" +
            "select * from wsbsj")
    List<CxtjgjzdVo> getCxtjgjzd(Integer nd);
}
